from sqlalchemy import *
from migrate import *

metadata = MetaData()

project_table = Table('projects', metadata, 
        Column('id', Integer, primary_key=True),
        Column('name', Unicode(100), nullable=False),
        Column('description', Text, nullable=True),
        Column('limit_to_8', Boolean, default=False),
        Column('horizon_from', Date, nullable=True), 
        Column('horizon_to', Date, nullable=True), 
        Column('time_step', Integer, nullable=True),
        Column('projection_params', PickleType(mutable=False), nullable=True),
        Column('projection_name', Unicode(250), nullable=True),
        Column('projection_epsg', Unicode(25), nullable=True)
)

network_table = Table('networks', metadata,
        Column('id', Integer, primary_key=True),
        Column('name', Unicode(100), nullable=False),
        Column('project_id', Integer, ForeignKey('projects.id'), \
               nullable=False),
        Column('description', Text, nullable=True)
)

nodetype_table = Table('nodetypes', metadata,
        Column('id', Integer, primary_key=True),
        Column('name', Unicode(100), nullable=False),
        Column('description', Text, nullable=True),
        Column('project_id', Integer, ForeignKey('projects.id'), \
               nullable=False),
        Column('shape', Unicode(25), nullable=True),
        Column('pen', Unicode(25), nullable=True),
        Column('brush', Unicode(25), nullable=True),
        Column('size', Integer, nullable=True),
        UniqueConstraint('name', 'project_id')
)

linktype_table = Table('linktypes', metadata,
        Column('id', Integer, primary_key=True),
        Column('name', Unicode(100), nullable=False),
        Column('description', Text, nullable=True),
        Column('pen', Unicode(25), nullable=True),
        Column('size', Integer, nullable=True),
        Column('style', Unicode(25), nullable=True),
        Column('project_id', Integer, ForeignKey('projects.id'), \
               nullable=False),
        UniqueConstraint('name', 'project_id')
)

nodetypefield_table = Table('nodetypefields', metadata,
        Column('id', Integer, primary_key=True),
        Column('node_type_id', Integer, ForeignKey('nodetypes.id'), \
               nullable=False),
        Column('attribute_type', PickleType, nullable=False),
        Column('name', Unicode(25), nullable=False),
        Column('group_id', Integer, ForeignKey('fieldgroups.id'), \
               nullable=False),
        UniqueConstraint('name', 'node_type_id')
)

linktypefield_table = Table('linktypefields', metadata,
        Column('id', Integer, primary_key=True),
        Column('link_type_id', Integer, ForeignKey('linktypes.id'), \
               nullable=False),
        Column('attribute_type', PickleType, nullable=False),
        Column('name', Unicode(25), nullable=False),
        Column('group_id', Integer, ForeignKey('fieldgroups.id'), \
               nullable=False),
        UniqueConstraint('name', 'link_type_id')
)

fieldgroup_table = Table('fieldgroups', metadata,
        Column('id', Integer, primary_key=True),
        Column('name', Unicode(20), nullable=False)
)

nodetype_fieldgroup_table = Table('ntype_groupe', metadata,
        Column('node_type_id', Integer, ForeignKey('nodetypes.id'),
               nullable=False, primary_key=True),
        Column('group_id', Integer, ForeignKey('fieldgroups.id'), 
               nullable=False, primary_key=True)
)

linktype_fieldgroup_table = Table('ltype_groupe', metadata,
        Column('link_type_id', Integer, ForeignKey('linktypes.id'),
               nullable=False, primary_key=True),
        Column('group_id', Integer, ForeignKey('fieldgroups.id'), 
               nullable=False, primary_key=True)
)

nodeattribute_table = Table('nodeattributes', metadata, 
        Column('node_id', Integer, ForeignKey('nodes.id'), primary_key=True),
        Column('attribute_id', Integer, ForeignKey('nodetypefields.id'), \
               primary_key=True),
        Column('value', PickleType)
)


linkattribute_table = Table('linkattributes', metadata, 
        Column('link_id', Integer, ForeignKey('links.id'), primary_key=True),
        Column('attribute_id', Integer, ForeignKey('linktypefields.id'), \
               primary_key=True),
        Column('value', PickleType)
)
    
node_table = Table('nodes', metadata, 
        Column('id', Integer, primary_key=True),
        Column('project_id', Integer, ForeignKey('projects.id'), \
               nullable=False),
        Column('type_id', Integer, ForeignKey('nodetypes.id'), nullable=False),
        Column('name', Unicode(100), nullable=False),
        Column('notes', Text, nullable=True, unique=False),
        Column('x', FLOAT, nullable=True),
        Column('y', FLOAT, nullable=True),
        UniqueConstraint('name', 'project_id')
) 

link_table = Table('links', metadata, 
        Column('id', Integer, primary_key=True),
        Column('project_id', Integer, ForeignKey('projects.id'), \
               nullable=False),
        Column('start_node_id', Integer, ForeignKey('nodes.id'), \
               nullable=False),
        Column('end_node_id', Integer, ForeignKey('nodes.id'), nullable=False),
        Column('type_id', Integer, ForeignKey('linktypes.id'), nullable=False),
        Column('geometry', Text, nullable=True),
        Column('name', Unicode(100), nullable=False, unique=False),
        Column('notes', Text, nullable=True, unique=False),
        UniqueConstraint('name', 'project_id')
)

network_node_table = Table('network_nodes', metadata,
        Column('network_id', ForeignKey('networks.id'), primary_key=True),
        Column('node_id', ForeignKey('nodes.id'), primary_key=True)
)

network_link_table = Table('network_links', metadata,
        Column('network_id', ForeignKey('networks.id'), primary_key=True),
        Column('link_id', ForeignKey('links.id'), primary_key=True)
)

nodetype_linktype_table = Table('nodetypes_linktypes', metadata,
        Column('nodetype_id', ForeignKey('nodetypes.id'), primary_key=True),
        Column('linktype_id', ForeignKey('linktypes.id'), primary_key=True)
)


units_table = Table('units', metadata,
        Column('id', Integer, primary_key=True),
        Column('name', Unicode(20), unique=True, nullable=False),
        Column('description', Text, nullable=True, unique=False),
        Column('category', Text, nullable=False, unique=False),
        )

project_units_table = Table('project_units', metadata,
            Column('project_id', ForeignKey('projects.id'), nullable=False, \
                   primary_key=True),
            Column('unit_id', ForeignKey('units.id'), nullable=False, \
                   primary_key=True))

def upgrade():
    # Upgrade operations go here. Don't create your own engine; use the engine
    # named 'migrate_engine' imported from migrate.
	metadata.create_all(migrate_engine)

def downgrade():
    # Operations to reverse the above upgrade go here.
	metadata.drop_all(migrate_engine)
